# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html

# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
from doubantop.settings import MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB_NAME
# import pymongo
# from doubantop.settings import mongo_host,mongo_port,mongo_db_name,mongo_db_collection


class DoubantopPipeline:
    def __init__(self):
        # 连接数据库
        self.db_conn = pymysql.connect(host=MYSQL_HOST,
                                       port=MYSQL_PORT,
                                       user=MYSQL_USER,
                                       passwd=MYSQL_PASSWORD,
                                       db=MYSQL_DB_NAME)
        # 获取数据库游标
        self.cursor = self.db_conn.cursor()
        self.cursor.execute(
            "DROP TABLE douban_movie;"
        )
        self.cursor.execute(
            "CREATE TABLE IF NOT EXISTS douban_movie ( serial_number VARCHAR(255), movie_name VARCHAR(255), information VARCHAR(255),  star VARCHAR(255), evaluate VARCHAR(255), description VARCHAR(255));"
        )

    def process_item(self, item, spider):
        # sql语句
        insert_sql = """
        INSERT INTO douban_movie (serial_number,movie_name,information,star,evaluate,description) VALUES (%s,%s,%s,%s,%s,%s)
        """
        # 插入数据到数据库
        self.cursor.execute(
            insert_sql,
            (item['serial_number'], item['movie_name'], item['information'],
             item['star'], item['evaluate'], item['description']))
        # 提交数据，否则无法保存到数据库
        self.db_conn.commit()

    def close_spider(self, spider):
        # 关闭游标和连接
        self.cursor.close()
        self.db_conn.close()


# mongo数据库
# class DoubantopPipeline:
#     def __init__(self):
#         host = mongo_host
#         port = mongo_port
#         dbname = mongo_db_name
#         sheetname = mongo_db_collection
#         client = pymongo.MongoClient(host=host,port=port)
#         mydb = client[dbname]
#         self.post = mydb[sheetname]
#     def process_item(self, item, spider):
#         data = dict(item)
#         self.post.insert(data)
#         return item
